2022 iThome 鐵人賽

DAY 10

Software Development

ClickHouse:時序資料庫建置與運行系列第 10 篇

day10-SQL使用與操作方法介紹(一)

14th鐵人賽

peter279k

2022-09-24 12:05:43

1598 瀏覽

分享至

前言

在前一章節中，介紹了各種利用ClickHouse的客戶端進行資料庫的連線，在這一章節中將會介紹基本的資料庫使用與操作的方法。

建立資料庫

在先前的章節中有提到，ClickHouse資料庫實做了SQL查詢解析器引擎，因此我們可以透過ClickHouse客戶端執行建立資料庫的SQL語法，同時相關的SQL語法關鍵字大小寫皆可以接受；相關的執行指令與輸出的訊息如下：

peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ clickhouse-client --password
ClickHouse client version 22.8.4.7 (official build).
Password for user (default):
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 22.8.4 revision 54460.

Warnings:
 * Linux is not using a fast TSC clock source. Performance can be degraded. Check /sys/devices/system/clocksource/clocksource0/current_clocksource

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) create database if not exists HELLOWORLD

CREATE DATABASE IF NOT EXISTS HELLOWORLD

Query id: 1d31dd34-5904-4f94-9467-9379c851dd09

Ok.

0 rows in set. Elapsed: 0.005 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

從上述的指令與輸出的訊息可以得知，若HELLOWORLD資料庫不存在，則我們在當前的ClickHouse資料庫上建立了叫做HELLOWORLD的資料庫，我們同時也可以使用show databases的SQL語法查看資料庫的列表，相關的執行SQL語法與輸出的訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) show databases;

SHOW DATABASES

Query id: c585e613-795b-4b71-b517-972d1f785817

┌─name───────────────┐
│ HELLOWORLD         │
│ INFORMATION_SCHEMA │
│ default            │
│ information_schema │
│ system             │
└────────────────────┘

5 rows in set. Elapsed: 0.001 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

這時候，從上面的指令輸出訊息可以得知，我們已經成功的將HELLOWORLD的資料庫建立完成了，並列出資料庫列表來確認這件事情。

建立資料表

建立好HELLOWORLD資料庫之後，接著我們可以在此上面的資料庫建立資料表，我們可以透過下列的指令來進行建立資料表，相關執行的SQL指令如下：

CREATE TABLE HELLOWORLD.my_first_table
(
    user_id UInt32,
    message String,
    timestamp DateTime,
    metric Float32
)
ENGINE = MergeTree()
PRIMARY KEY (user_id, timestamp)

從上面的SQL指令我們可以知道，在建立資料表的時候，需要指定使用的資料表引擎，而資料表引擎會決定有關於這個資料表的下列事情：

資料將如何存放與存放在哪裡。
哪一些的SQL查詢可以支援與使用。
是否資料可以是被複製的(replicated)。

在ClickHouse資料庫中，有很多的資料表引擎可以選擇與使用，但是對於在單一節點的ClickHouse資料庫上的簡易資料表MergeTree資料表引擎是我們合適的選項，上述的SQL指令就是將my_first_table的資料表建立在HELLOWORLD資料庫中，相關的執行指令與輸出的訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) CREATE TABLE HELLOWORLD.my_first_table
                                  (
                                      user_id UInt32,
                                      message String,
                                      timestamp DateTime,
                                      metric Float32
                                  )
                                  ENGINE = MergeTree()
                                  PRIMARY KEY (user_id, timestamp)

CREATE TABLE HELLOWORLD.my_first_table
(
    `user_id` UInt32,
    `message` String,
    `timestamp` DateTime,
    `metric` Float32
)
ENGINE = MergeTree
PRIMARY KEY (user_id, timestamp)

Query id: 4f99aa2d-8e4e-424f-b799-d6bd495d91fa

Ok.

0 rows in set. Elapsed: 0.009 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

簡短說明資料表主鍵

在往下其他的章節之前，很重要的事情是需要先了解主鍵是如何在ClickHouse資料庫運作的，因為主鍵在ClickHouse資料庫實做上是與一般我們在想像上是不同的，即主鍵在ClickHose資料庫中對於在資料表中每一行資料不是唯一的。

ClickHouse資料表的主鍵是拿來決定如何將資料寫入到硬碟，對於8,192行的資料或是10MB大小的資料，會在每個主鍵索引檔案中建立每一個項目entry，這每個項目顆粒度(granularity)指的是會建立稀疏索引(sparse index)，這個機制可以容易的在記憶體中處理，且顆粒度呈現了列資料的最小量，這樣一來可以容易地SELECT查詢上進行處理。

主鍵的定義可以在建立資料表時，利用PRIMARY KEY的關鍵字來達成，如果我們定義沒有PRIMARY KEY之關鍵字的資料表，則鍵則會在建立資料表時使用排序ORDER BY時候，變成序列(tuple)形式。

如果我們在建立資料表時，同時指定PRIMARY KEY與ORDER BY語句時，則主鍵必須是排序的子集，即使用PRIMARY KEY特性是包含在ORDER BY的。

在上述建立資料表的範例，我們建立叫做my_first_table的MergeTree資料表並有著四個欄位如下：

user_id，32位元無符號(unsigned)的整數。
message，字串資料型別，等價於在其他的資料庫系統的資料型別則是VARCHAR、BLOB與CLOB等。
timestamp: 日期時間的型別，表示某個時間點的時間。
metric，32位元的浮點數。

主鍵也是排序用的鍵，以上述的建立資料表來說，(user_id, timestamp)表示是一個序列。因此，資料在儲存時候，每筆資料在存放到欄位檔案時，會先以user_id排序；接著再以timestamp進行排序。

寫入資料

在ClickHouse資料庫中，我們可以使用大家熟悉的INSERT INTO TABLE的SQL語句來進行寫入資料的動作，但是更重要的是，我們需要了解每筆資料寫入到MergeTree資料表時，會在硬碟儲存空間建立某個部份。

對於使用ClickHouse資料庫的良好經驗來說，一個批次，可以寫入一個數量很大的資料筆數；即一次可以寫入數十萬或甚至是數百萬。且ClickHouse官方保證可以輕易地處理上述這件事情。

下列是一個寫入資料的SQL語句範例：

INSERT INTO HELLOWORLD.my_first_table (user_id, message, timestamp, metric) VALUES
    (101, 'Hello, ClickHouse!',                                 now(),       -1.0    ),
    (102, 'Insert a lot of rows per batch',                     yesterday(), 1.41421 ),
    (102, 'Sort your data based on your commonly-used queries', today(),     2.718   ),
    (101, 'Granules are the smallest chunks of data read',      now() + 5,   3.14159 )

相關執行上述的SQL指令的輸出訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) INSERT INTO HELLOWORLD.my_first_table (user_id, message, timestamp, metric) VALUES
                                      (101, 'Hello, ClickHouse!',                                 now(),       -1.0    ),
                                      (102, 'Insert a lot of rows per batch',                     yesterday(), 1.41421 ),
                                      (102, 'Sort your data based on your commonly-used queries', today(),     2.718   ),
                                      (101, 'Granules are the smallest chunks of data read',      now() + 5,   3.14159 )

INSERT INTO HELLOWORLD.my_first_table (user_id, message, timestamp, metric) FORMAT Values

Query id: 13201d69-4945-44f7-aa6f-f79c79c66153

Ok.

4 rows in set. Elapsed: 0.007 sec.

需要注意的是，timestamp欄位可以用多種日期Date與日期時間DateTime的內建函式，像是上述使用的now()就是其中一種，ClickHouse資料庫有上百個的實用內建函式，我們會在後續的章節提提到。

接著我們可以使用SELECT的查詢語句來驗證上述的資料是否已經有寫入進去，相關的SQL語句執行與輸出的訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) SELECT * from HELLOWORLD.my_first_table;

SELECT *
FROM HELLOWORLD.my_first_table

Query id: aed18b2f-a066-4eaa-8b6b-970a2b159e9f

┌─user_id─┬─message────────────────────────────────────────────┬───────────timestamp─┬──metric─┐
│     101 │ Hello, ClickHouse!                                 │ 2022-09-12 03:16:52 │      -1 │
│     101 │ Granules are the smallest chunks of data read      │ 2022-09-12 03:16:57 │ 3.14159 │
│     102 │ Insert a lot of rows per batch                     │ 2022-09-11 00:00:00 │ 1.41421 │
│     102 │ Sort your data based on your commonly-used queries │ 2022-09-12 00:00:00 │   2.718 │
└─────────┴────────────────────────────────────────────────────┴─────────────────────┴─────────┘

4 rows in set. Elapsed: 0.002 sec.

同時，我們在使用查詢語句時，也可以加入FORMAT的關鍵字，在查詢完之後的資料轉成其他的資料格式，下面的範例是將輸出的資料轉成Vertical的資料格式，相關的SQL語句如下：

SELECT *
FROM HELLOWORLD.my_first_table
ORDER BY timestamp ASC
FORMAT Vertical

執行上述的SQL語句與輸出的訊息結果如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) SELECT *
                                  FROM HELLOWORLD.my_first_table
                                  ORDER BY timestamp ASC
                                  FORMAT Vertical

SELECT *
FROM HELLOWORLD.my_first_table
ORDER BY timestamp ASC
FORMAT Vertical

Query id: 4f6190b9-0a3a-4078-a982-5d9d5ea505bb

Row 1:
──────
user_id:   102
message:   Insert a lot of rows per batch
timestamp: 2022-09-11 00:00:00
metric:    1.41421

Row 2:
──────
user_id:   102
message:   Sort your data based on your commonly-used queries
timestamp: 2022-09-12 00:00:00
metric:    2.718

Row 3:
──────
user_id:   101
message:   Hello, ClickHouse!
timestamp: 2022-09-12 03:16:52
metric:    -1

Row 4:
──────
user_id:   101
message:   Granules are the smallest chunks of data read
timestamp: 2022-09-12 03:16:57
metric:    3.14159

4 rows in set. Elapsed: 0.004 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

從上述的輸出結果可以知道，Vertical的資料格式是將輸出的結果以垂直方式將資料以清單的方式呈現，還有其他資料格式的輸出方式我們會在後續的章節中提到。

寫入CSV檔案的資料

在使用資料庫的時候，常見的任務是利用已經存在的檔案將裡面的資料內容寫入到ClickHouse資料庫中。假設我們有一個CSV檔案叫做data.csv，其檔案的內容如下：

102,This is data in a file,2022-02-22 10:43:28,123.45
101,It is comma-separated,2022-02-23 00:00:00,456.78
103,Use FORMAT to specify the format,2022-02-21 10:43:30,678.90

則我們可以使用下列的指令將上述的CSV檔案寫入到指定的資料表中，相關的SQL語句如下：

clickhouse-client --password \
    --query='INSERT INTO HELLOWORLD.my_first_table FORMAT CSV' < data.csv

相關的指令操作與輸出的訊息如下所示：

peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ vim data.csv
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ clickhouse-client --password \
>     --query='INSERT INTO HELLOWORLD.my_first_table FORMAT CSV' < data.csv
Password for user (default):
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$

從上述的執行指令可以看到，首先先利用vim文字編輯器進行data.csv檔案與內容的建立，接著執行clickhouse-client指令並利用--query設定INSERT INTO參數並將檔案內容利用<與SQL語句結合並執行該SQL語句。

接著可以執行下列的SQL語句並驗證上述的輸入資料，相關的SQL語句如下：

SELECT *
   FROM HELLOWORLD.my_first_table
   ORDER BY timestamp ASC

執行上述的SQL語句與輸出的訊息如下：

peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ clickhouse-client --password
ClickHouse client version 22.8.4.7 (official build).
Password for user (default):
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 22.8.4 revision 54460.

Warnings:
 * Linux is not using a fast TSC clock source. Performance can be degraded. Check /sys/devices/system/clocksource/clocksource0/current_clocksource

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) SELECT *
                                     FROM HELLOWORLD.my_first_table
                                     ORDER BY timestamp ASC

SELECT *
FROM HELLOWORLD.my_first_table
ORDER BY timestamp ASC

Query id: 70ec0b4e-292c-4d7c-b235-02928ad1a73d

┌─user_id─┬─message──────────────────────────┬───────────timestamp─┬─metric─┐
│     103 │ Use FORMAT to specify the format │ 2022-02-21 10:43:30 │  678.9 │
│     102 │ This is data in a file           │ 2022-02-22 10:43:28 │ 123.45 │
│     101 │ It is comma-separated            │ 2022-02-23 00:00:00 │ 456.78 │
└─────────┴──────────────────────────────────┴─────────────────────┴────────┘
┌─user_id─┬─message────────────────────────────────────────────┬───────────timestamp─┬──metric─┐
│     102 │ Insert a lot of rows per batch                     │ 2022-09-11 00:00:00 │ 1.41421 │
│     102 │ Sort your data based on your commonly-used queries │ 2022-09-12 00:00:00 │   2.718 │
│     101 │ Hello, ClickHouse!                                 │ 2022-09-12 03:16:52 │      -1 │
│     101 │ Granules are the smallest chunks of data read      │ 2022-09-12 03:16:57 │ 3.14159 │
└─────────┴────────────────────────────────────────────────────┴─────────────────────┴─────────┘

7 rows in set. Elapsed: 0.004 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

從上述的結果來看，在輸出的結果我們可以看到有兩個集合的資料行數rows，這意謂著我們的資料分成在多個部分並存放在硬碟上，這個情形預期上來說是會發生的原因是我們執行了兩次寫入資料的語句，一個寫入資料語句是利用ClickHouse的資料庫客戶端完成，另一個則是利用CSV檔案寫入資料來完成。這部份與MergeTree資料表引擎機制有關，這將會在後續的章節中提到。